<?php


class ResearchTable extends Doctrine_Table
{
    
    public static function getInstance()
    {
        return Doctrine_Core::getTable('Research');
    }

    /*
     * Returns list of all researches user is collaborating on
     */
    public function getAllCollaborationReseachList($entityId)
    {
        $items = Doctrine_Query::create()
                ->select('r.id, r.title, r.citation')
                ->from('Research r');
    }

    public function getAllApprovalRequestsCountByStatus($status, $approvalAuthorityId) {
        if (!is_array($status))
            $status = array($status);

        $items = Doctrine_Query::create()
                ->select('r.id, r.title, r.citation')
                ->from('Research r')
                ->leftJoin('r.Observation o')
                ->leftJoin('o.Observationdepth od')
                ->leftJoin('od.Observationdepthattribute oda')
                ->leftJoin('oda.Attribute a')
                ->leftJoin('od.Observationdepthdetail odd')
                ->leftJoin('odd.Species s')
                ->leftJoin('r.Researchoverlay ro')
                ->Where('r.approvalstatustyperefid in ? ' .
                        'OR o.approvalstatustyperefid in ? ' .
                        'OR od.approvalstatustyperefid in ? ' .
                        'OR oda.approvalstatustyperefid in ? ' .
                        'OR odd.approvalstatustyperefid in ? ' .
                        'OR a.approvalstatustyperefid in ? ' .
                        'OR s.approvalstatustyperefid in ? ' .
                        'OR ro.approvalstatustyperefid in ? ',
                        array($status, $status, $status, $status, $status,
                            $status, $status, $status))
                ->AddWhere('r.approvalauthorityid in ? ' .
                        'OR o.approvalauthorityid in ? ' .
                        'OR od.approvalauthorityid in ? ' .
                        'OR oda.approvalauthorityid in ? ' .
                        'OR odd.approvalauthorityid in ? ' .
                        'OR a.approvalauthorityid in ? ' .
                        'OR s.approvalauthorityid in ? ' .
                        'OR ro.approvalauthorityid in ? ',
                        array($approvalAuthorityId, $approvalAuthorityId, $approvalAuthorityId,
                            $approvalAuthorityId, $approvalAuthorityId, $approvalAuthorityId,
                            $approvalAuthorityId, $approvalAuthorityId));

        return $items;
    }

    public function getAllApprovalRequestsByStatus($status, $approvalAuthorityId, $pageNumber, $Searchtext) {
        if (!is_array($status))
            $status = array($status);

        $pager = new sfDoctrinePager('Research', sfConfig::get('app_Research_maxItemsPerPage'));

        $query = '';
        $query = Doctrine_Query::create()
                ->select('r.id, r.title, r.citation')
                ->from('Research r')
                ->leftJoin('r.Observation o')
                ->leftJoin('o.Observationdepth od')
                ->leftJoin('od.Observationdepthattribute oda')
                ->leftJoin('oda.Attribute a')
                ->leftJoin('od.Observationdepthdetail odd')
                ->leftJoin('odd.Species s')
                ->leftJoin('r.Researchoverlay ro')
                ->Where('r.approvalstatustyperefid in ? ' .
                        'OR o.approvalstatustyperefid in ? ' .
                        'OR od.approvalstatustyperefid in ? ' .
                        'OR oda.approvalstatustyperefid in ? ' .
                        'OR odd.approvalstatustyperefid in ? ' .
                        'OR a.approvalstatustyperefid in ? ' .
                        'OR s.approvalstatustyperefid in ? ' .
                        'OR ro.approvalstatustyperefid in ? ',
                        array($status, $status, $status, $status, $status,
                            $status, $status, $status))
                ->AddWhere('r.approvalauthorityid in ? ' .
                        'OR o.approvalauthorityid in ? ' .
                        'OR od.approvalauthorityid in ? ' .
                        'OR oda.approvalauthorityid in ? ' .
                        'OR odd.approvalauthorityid in ? ' .
                        'OR a.approvalauthorityid in ? ' .
                        'OR s.approvalauthorityid in ? ' .
                        'OR ro.approvalauthorityid in ? ',
                        array($approvalAuthorityId, $approvalAuthorityId, $approvalAuthorityId,
                            $approvalAuthorityId, $approvalAuthorityId, $approvalAuthorityId,
                            $approvalAuthorityId, $approvalAuthorityId));

        // SEARCHTEXT   => Text user types in inputer control for searching
        if(isset ($Searchtext))
            $query->andWhere("r.title like '%".$Searchtext."%'");

        $query->orderby('r.created_at desc');

        $pager->setQuery($query);
        $pager->setPage($pageNumber);
        $pager->init();

        return $pager;
    }
}